import datetime

from sqlalchemy import text

from py.PojosAndDaos.Account import Account_class
from py.PojosAndDaos.Book import Book_class
from py.PojosAndDaos.Record import Record_class
from py.PojosAndDaos.Message import Message_class


class MessageService:
    @classmethod
    def Message_Insert(cls, message: Message_class()):
        session5 = Message_class().session
        temp = datetime.datetime.now()
        session5.execute(
            text("INSERT INTO MESSAGE (ReaderID, ReaderName, message, MESSAGE.datetime) VALUES ('{}','{}','{}','{}');".
                 format(message.ReaderID, message.ReaderName, message.message, temp)))
        session5.commit()
        session5.close()
        return "留言成功"

    @classmethod
    def Message_Query_All(cls):
        session5 = Message_class().session
        messageList = session5.query(Message_class()).all()
        session5.close()
        return messageList

    @classmethod
    def Message_Query_ByID(cls, ID: int):
        session5 = Message_class().session
        messageList = session5.query(Message_class()).filter_by(ReaderID=ID)
        session5.close()
        return messageList

    # 根据时间间隔删除信息
    @classmethod
    def Message_Delete_ByDuration(cls, Duration: int):
        session5 = Message_class().session
        messageList = session5.query(Message_class()).filter(
            text("DATEDIFF( '{}', DATETIME ) >= '{}'".format(datetime.date.today(), Duration))
        ).delete()
        session5.commit()
        session5.close()
        return "删除成功！"

    @classmethod
    def Message_Delete_ByID(cls, ID: int):
        session5 = Message_class().session
        session5.query(Message_class()).filter_by(ReaderID=ID).delete()
        session5.commit()
        session5.close()
        return "删除成功！"

    @classmethod
    def Message_Delete_ByDateTime(cls, datetime: datetime.datetime):
        session5 = Message_class().session
        session5.query(Message_class()).filter(Message_class().datetime < datetime).delete()
        session5.commit()
        session5.close()
        return "删除成功！"


if __name__ == '__main__':
    MessageService.Message_Delete_ByDuration(5)
